package com.easyJava.builder;


import com.easyJava.bean.Constants;
import com.easyJava.bean.FieldInfo;
import com.easyJava.bean.TableInfo;
import com.easyJava.utils.PropertiesUtils;
import com.easyJava.utils.StringUtils;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ArrayUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@ToString
@Slf4j
public class BuildTable {
    private static Connection connection = null;
    private static final String SQL_SHOW_TABLE_STATUS = "show table status";
    private static final String SQL_SHOW_FULL_COLUMNS = "show full columns from %s";
    private static final String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driver = PropertiesUtils.getProperties("db.driver");
        String url = PropertiesUtils.getProperties("db.url");
        String username = PropertiesUtils.getProperties("db.username");
        String password = PropertiesUtils.getProperties("db.password");

        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("数据库连接失败");
        }
    }


    public static List<TableInfo> getTable(){
        Statement statement = null;
        ResultSet set = null;
        List<TableInfo> tableList = new ArrayList<>();
        try{
            statement = connection.createStatement();
            set = statement.executeQuery(SQL_SHOW_TABLE_STATUS);
            while(set.next()){
                String tableName = set.getString("Name");
                String tableComment = set.getString("Comment");
                TableInfo tableInfo = new TableInfo();
                String beanName = tableName;
                if(Constants.IGNORE_TABLE_PREFIX){
                    beanName = tableName.substring(tableName.indexOf("_")+1);
                }
                beanName = processFiled(beanName, true);
                tableInfo.setTableName(tableName);
                tableInfo.setComment(tableComment);
                tableInfo.setBeanName(beanName);
                tableInfo.setBeanQueryName(beanName + Constants.SUFFIX_BEAN_QUERY);
                getFiled(tableInfo);
                getIndex(tableInfo);

                tableList.add(tableInfo);
            }
        }catch (Exception e){
            e.printStackTrace();
            log.error("查询表失败:{}", e);
        }finally {
            if(set != null){
                try {
                    set.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return tableList;
    }

    public static void getFiled(TableInfo tableInfo){
        Statement statement = null;
        ResultSet set = null;
        List<FieldInfo> fieldInfos = new ArrayList<>();
        List<FieldInfo> extendFieldList = new ArrayList<>();
        try{
            statement = connection.createStatement();
            set = statement.executeQuery(String.format(SQL_SHOW_FULL_COLUMNS, tableInfo.getTableName()));
            while(set.next()){
                String fieldName = set.getString("Field");
                String sqlType = set.getString("Type");
                if(sqlType.indexOf("(") != -1){
                    sqlType = sqlType.substring(0, sqlType.indexOf("("));
                }
                String extra = set.getString("Extra");
                String comment = set.getString("Comment");

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfos.add(fieldInfo);

                String beanFieldName = processFiled(fieldName, false);
                String javaType = processJavaType(sqlType);

                fieldInfo.setFieldName(fieldName);
                fieldInfo.setComment(comment);
                fieldInfo.setBeanFieldName(beanFieldName);
                fieldInfo.setAutoIncrement("auto_increment".equals(extra));
                fieldInfo.setSqlType(sqlType);
                fieldInfo.setJavaType(javaType);

                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, sqlType)){
                    tableInfo.setHaveBigDecimal(true);
                }else if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE, sqlType)){
                    tableInfo.setHaveDateTime(true);

                    FieldInfo startTime = new FieldInfo();
                    startTime.setJavaType(javaType);
                    startTime.setSqlType(sqlType);
                    startTime.setFieldName(fieldName);
                    startTime.setBeanFieldName(beanFieldName + Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    extendFieldList.add(startTime);

                    FieldInfo endTime = new FieldInfo();
                    endTime.setJavaType(javaType);
                    endTime.setSqlType(sqlType);
                    endTime.setFieldName(fieldName);
                    endTime.setBeanFieldName(beanFieldName + Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    extendFieldList.add(endTime);
                }else if(ArrayUtils.contains(Constants.SQL_DATE_TYPE, sqlType)){
                    tableInfo.setHaveDate(true);

                    FieldInfo startTime = new FieldInfo();
                    startTime.setJavaType(javaType);
                    startTime.setSqlType(sqlType);
                    startTime.setFieldName(fieldName);
                    startTime.setBeanFieldName(beanFieldName + Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    extendFieldList.add(startTime);

                    FieldInfo endTime = new FieldInfo();
                    endTime.setJavaType(javaType);
                    endTime.setSqlType(sqlType);
                    endTime.setFieldName(fieldName);
                    endTime.setBeanFieldName(beanFieldName + Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    extendFieldList.add(endTime);
                }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPE, sqlType)){
                    FieldInfo fuzzyQuery = new FieldInfo();
                    fuzzyQuery.setJavaType(javaType);
                    fuzzyQuery.setSqlType(sqlType);
                    fuzzyQuery.setFieldName(fieldName);
                    fuzzyQuery.setBeanFieldName(beanFieldName + Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    extendFieldList.add(fuzzyQuery);
                }

                if(ArrayUtils.contains(Constants.IGNORE_FIELDS, fieldName)){
                    tableInfo.setHaveIgnoreField(true);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            log.error("查询字段失败:{}", e);
        }finally {
            if(set != null){
                try {
                    set.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        tableInfo.setFieldList(fieldInfos);
        tableInfo.setExtendFieldList(extendFieldList);
    }

    public static void getIndex(TableInfo tableInfo){
        Statement statement = null;
        ResultSet set = null;
        Map<String, List<FieldInfo>> indexMap = tableInfo.getIndexMap();

        Map<String, FieldInfo> tempMap = new HashMap<>();
        for(FieldInfo fieldInfo : tableInfo.getFieldList()) {
            tempMap.put(fieldInfo.getFieldName(), fieldInfo);
        }
        try{
            statement = connection.createStatement();
            set = statement.executeQuery(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            while(set.next()){
                String keyName = set.getString("Key_name");
                int nonUnique = set.getInt("Non_unique");
                String columnName = set.getString("Column_name");

                if(nonUnique == 1) continue;

                List<FieldInfo> indexFields = indexMap.get(keyName);
                if(indexFields == null){
                    indexFields = new ArrayList<>();
                    indexMap.put(keyName, indexFields);
                }
                indexFields.add(tempMap.get(columnName));
            }
        }catch (Exception e){
            e.printStackTrace();
            log.error("查询索引失败:{}", e);
        }finally {
            if(set != null){
                try {
                    set.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }



    private static String processFiled(String field, boolean upperCaseFirstLetter){
        String[] split = field.split("_");
        StringBuffer sb = new StringBuffer();
        if(upperCaseFirstLetter){
            sb.append(StringUtils.upperFirstChar(split[0]));
        }else {
            sb.append(split[0]);
        }
        for(int i=1; i<split.length; i++){
            sb.append(StringUtils.upperFirstChar(split[i]));
        }
        return sb.toString();
    }

    private static String processJavaType(String sqlType){
        if(ArrayUtils.contains(Constants.SQL_INTEGER_TYPE, sqlType)){
            return "Integer";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPE, sqlType)){
            return "LocalDateTime";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TYPE, sqlType)){
            return "LocalDate";
        }else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, sqlType)){
            return "BigDecimal";
        }else if(ArrayUtils.contains(Constants.SQL_LONG_TYPE, sqlType)){
            return "Long";
        }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPE, sqlType)){
            return "String";
        }else {
            throw new RuntimeException("无法识别的类型" + sqlType);
        }
    }

}
